# coding:utf-8

import os
import json
from collections import namedtuple
from datetime import date, datetime, timedelta

from flask import (
    render_template, request, current_app,
    send_file, abort, redirect, url_for, flash
)
from flask_login import login_required
from sqlalchemy import func, case, or_
import xlwt

from common.constant import VoucherStatType
from app.war_zone.models import WarZoneDailyStatic
from ..extensions import db
from . import op_bp
from .models import (
    FrontendEventStat, OpcDayStat, WxUser, User,
    Partic, StockDayStat,
    RenewableDemand, UserInfo, get_timestamp,
    VoucherDayStat, VoucherDailyStat, DemandDayStat
)
from .stat_forms import (
    SearchEventStatForm, DefaultStatSearchForm,
    SearchWechatStatForm, SearchFeedbackStatForm,
    SearchAppPromotionStatForm,
    SearchAppPromotionCensForm,
    OperateExportForm)
from app.op.constant import (
    WxInviteState, UserType, ParticStatus,
    FeedbackStatus, DemandStatus, OperatedStatus)


@op_bp.route('/event_stat_list', methods=['GET', 'POST'])
@login_required
def event_stat_list():
    form = SearchEventStatForm(formdata=request.form or request.args)

    stat_query = FrontendEventStat.query.filter_by(deleted_at=0)
    url_kwargs = {}
    if form.validate():

        _, url_kwargs = form.generate_kwargs()

        stat_query = FrontendEventStat.generate_list_query_condition(
            args=url_kwargs,
            query=stat_query
        )

    page = request.args.get('page', 1, type=int)
    pagination = stat_query.order_by(FrontendEventStat.created_at.desc()).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    stats = pagination.items

    return render_template(
        'stat/event_stat_list.html',
        form=form,
        stats=stats,
        pagination=pagination,
        url_kwargs=url_kwargs,
    )


@op_bp.route('/day_stat_list', methods=['GET', 'POST'])
@login_required
def day_stat_list():
    """
    created by chenhj at 2017/9/8
    opc日统计
    """
    form = DefaultStatSearchForm(formdata=request.form or request.args)
    today = date.today()
    if not form.start_date.data:
        form.start_date.data = date(today.year, today.month, 1)
    stat_query = OpcDayStat.query.filter_by(deleted_at=0).order_by(OpcDayStat.stat_date.desc())
    url_kwargs = {}
    if form.validate():

        _, url_kwargs = form.generate_kwargs()

        stat_query = OpcDayStat.generate_list_query_condition(
            args=url_kwargs,
            query=stat_query
        )

    page = request.args.get('page', 1, type=int)
    pagination = stat_query.paginate(
        page, per_page=500,
        error_out=False)
    stats = pagination.items

    partic_info = OpcDayStat.get_partic_cnt(today)
    pay_info = OpcDayStat.get_pay_cnt(today)
    bid_point_info = OpcDayStat.get_day_cost_bid_point(today)
    ro_info = OpcDayStat.get_ro_cnt(today)
    voucher_info = OpcDayStat.get_voucher_stat(today)
    today_data = {
        'ro_cnt': ro_info[0],
        'publish_count': ro_info[1],
        'bid_cnt': partic_info[0],
        'bid_people_cnt': partic_info[1],
        'pay_people_cnt': pay_info[1],
        'pay_cnt': pay_info[0],
        'pay_amt': pay_info[2][0] or 0,
        'day_cost_bid_point': bid_point_info[0],
        'day_cost_bid_point_dispatch': bid_point_info[1],
        'day_cost_bid_point_bronze': bid_point_info[2],
        'day_cost_bid_point_other': bid_point_info[3],
        'day_return_bid_point': bid_point_info[4],
        'voucher_bid_point': 0 if not voucher_info[0] else voucher_info[0],
        'used_voucher_bid_point': 0 if not voucher_info[1] else voucher_info[1],
        'stat_date': today.strftime('%F')
    }

    return render_template(
        'stat/day_stat_list.html',
        form=form,
        stats=stats,
        pagination=pagination,
        url_kwargs=url_kwargs,
        today_data=today_data
    )


@op_bp.route('/wechat_invite_stat', methods=['GET', 'POST'])
@login_required
def wechat_invite_stat():
    """
    created by chenhj at 2017/9/20

    """
    form = SearchWechatStatForm(formdata=request.form or request.args)

    base_query = WxUser.query.join(
        User, WxUser.invite_user_id == User.id
    ).filter(
        WxUser.invite_user_id != 0
    ).with_entities(
        User.nickname,
        func.count(0).label('total'),
        func.sum(
            case([
                (WxUser.subscribe == 1, 1)
            ], else_=0)
        ).label('subscribe'),
        func.sum(
            case([
                (WxUser.invite_state == WxInviteState.BINDED.value, 1)
            ], else_=0)
        ).label('bind')
    ).group_by(WxUser.invite_user_id)

    if form.validate_on_submit():
        # 构造url参数，供pagination构造翻页url使用
        if form.invite_user_id.data != -1:
            base_query = base_query.filter(
                WxUser.invite_user_id == form.invite_user_id.data
            )
        if form.only_ground.data:
            base_query = base_query.filter(
                User.type == UserType.GROUND_MARKETER.value
            )

    page = request.args.get('page', 1, type=int)
    pagination = base_query.order_by(db.desc('total')).paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    data = pagination.items
    return render_template(
        'stat/wechat_invite_stat.html',
        data=data,
        pagination=pagination,
        url_kwargs={},
        form=form
    )


@op_bp.route('/demand_feedback_stat', methods=['GET', 'POST'])
@login_required
def demand_feedback_stat():
    """
    created by chenhj at 20/03/2018
    select user.mobile, user.nickname, count(partic.id) as feedback_cnt,
     sum(partic.offer) as feedback_offer,
      (select count(*) from partic where supplier_id=user.id and partic.status=200
            and feedback!=0 and partic.feedback_status=2 and demand_status=9) as feedback_disp_cnt,
      (select count(*) from partic where supplier_id=user.id and partic.status=200) as success_partic_cnt
    from partic join user on user.id = partic.supplier_id
    where partic.status=200 and partic.feedback!=0 and partic.feedback_status=2
    group by partic.supplier_id
    having feedback_cnt > 0
    order by feedback_offer desc
    """
    url_kwargs = {}
    form = SearchFeedbackStatForm(formdata=request.form or request.args)
    query = Partic.query.join(
        User, User.id == Partic.supplier_id
    ).filter(
        Partic.status == ParticStatus.SUCCEED.value,
        Partic.feedback != 0,
        Partic.feedback_status == FeedbackStatus.PASS.value
    )

    if form.validate() and form.mobile.data:
        query = query.filter(
            User.mobile == form.mobile.data
        )

    query = query.with_entities(
        User.id, User.mobile, User.nickname, func.count(Partic.id).label('feedback_cnt'),
        func.sum(Partic.offer).label('feedback_offer')
    ).group_by(
        Partic.supplier_id
    ).having(
        func.count(Partic.id) > 0
    ).order_by(
        func.count(Partic.id).desc()
    )

    page = request.args.get('page', 1, type=int)
    pagination = query.paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    stats = pagination.items
    res = []

    Stat = namedtuple('Stat', 'id mobile nickname feedback_cnt feedback_offer feedback_disp_cnt success_partic_cnt')

    for each in stats:
        feedback_disp_cnt = Partic.query.filter(
            Partic.supplier_id == each.id,
            Partic.status == ParticStatus.SUCCEED.value,
            Partic.feedback != 0,
            Partic.feedback_status == FeedbackStatus.PASS.value,
            Partic.demand_status.in_([
                DemandStatus.DISPATCHING.value, DemandStatus.MASTER_DISPATCHING.value
            ])
        ).count()
        success_partic_cnt = Partic.query.filter(
            Partic.supplier_id == each.id,
            Partic.status == ParticStatus.SUCCEED.value,
        ).count()
        res.append(Stat(
            each.id, each.mobile, each.nickname, each.feedback_cnt,
            int(each.feedback_offer), feedback_disp_cnt, success_partic_cnt)
        )

    return render_template(
        'stat/feedback_stat_list.html',
        stats=res,
        pagination=pagination,
        url_kwargs=url_kwargs,
        form=form
    )


@op_bp.route('/renewable_demand/stat', methods=['GET', 'POST'])
@login_required
def renewable_demand_stat():
    """
    create by ld_wu 2018-04-04
    :return: 
    """
    form = DefaultStatSearchForm(formdata=request.form or request.args)

    today = datetime.today()

    if not form.start_date.data:
        form.start_date.data = date(today.year, today.month, 1)
    stat_query = WarZoneDailyStatic.query.filter_by(
        deleted_at=0
    ).group_by(
        WarZoneDailyStatic.date
    ).with_entities(
        func.sum(WarZoneDailyStatic.ro_cnt).label('ro_cnt'),
        WarZoneDailyStatic.date.label('date')
    )
    renewable_query = RenewableDemand.query.filter(
        RenewableDemand.recovery_date.between(
            form.start_date.data,
            form.end_date.data
        )
    ).group_by(
        RenewableDemand.recovery_date
    ).with_entities(
        RenewableDemand.recovery_date,
        func.sum(
            case([
                (RenewableDemand.operated_status == OperatedStatus.TRAINING.value, 1)
            ], else_=0)
        ).label('training_cnt'),
        func.sum(
            case([
                (RenewableDemand.operated_status == OperatedStatus.INVALID.value, 1)
            ], else_=0)
        ).label('invalid_cnt'),
        func.sum(
            case([
                (RenewableDemand.operated_status == OperatedStatus.REPUBLISH.value, 1)
            ], else_=0)
        ).label('republish_cnt'),
        func.count().label('renewable_cnt'),
        func.sum(
            case([
                (RenewableDemand.recovery_success_count > 0, 1)
            ], else_=0)
        ).label('recovery_success_cnt')
    )
    url_kwargs = {}
    if form.validate():

        _, url_kwargs = form.generate_kwargs()

        stat_query = WarZoneDailyStatic.generate_list_query_condition(
            args=url_kwargs,
            query=stat_query
        )
        stat_query = stat_query.order_by(None)
    ro_stats = stat_query.all()
    ro_mapping = {str(each.date): each.ro_cnt for each in ro_stats}
    stats = renewable_query.all()

    return render_template(
        'stat/renewable_demand_stat.html',
        form=form,
        stats=stats,
        url_kwargs=url_kwargs,
        ro_mapping=ro_mapping
    )


@op_bp.route('/app/promotion/stat', methods=['GET', 'POST'])
@login_required
def app_promotion_stat():
    """
    created by chenhj at 2018/5/3

    """
    url_kwargs = {}
    form = SearchAppPromotionStatForm(
        formdata=request.form or request.args
    )

    query = User.query.join(
        UserInfo, User.id == UserInfo.user_id
    ).filter(
        User.is_internal.is_(False),
        User.is_virt.is_(False),
        User.type == UserType.ENGINEER.value,
        or_(
            UserInfo.app_invite_user_id != 0,
            User.id.in_(UserInfo.query.filter(
                UserInfo.app_invite_user_id != 0
            ).with_entities(UserInfo.app_invite_user_id.distinct()))
        )
    ).order_by(
        User.created_at.desc()
    )

    if form.validate():
        if form.mobile.data:
            query = query.filter(
                User.mobile == form.mobile.data
            )

        if (
            form.app_invite_user_id.data
            and form.app_invite_user_id.data > -1
        ):
            query = query.filter(
                UserInfo.app_invite_user_id == form.app_invite_user_id.data
            )

        if (
            form.app_root_invite_user_id.data
            and form.app_root_invite_user_id.data > -1
        ):
            query = query.filter(
                UserInfo.app_root_invite_user_id == form.app_root_invite_user_id.data
            )
    query = query.with_entities(
        User.id, User.mobile, User.nickname, User.created_at
    )

    page = request.args.get('page', 1, type=int)
    pagination = query.paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    stats = pagination.items
    res = []

    UserStat = namedtuple(
        'UserStat',
        'id mobile nickname invite_name invite_mobile root_name root_mobile invite_cnt created_at'
    )

    for each in stats:
        user_info = UserInfo.default_user_info(each.id)
        invite_name = ''
        invite_mobile = ''
        if user_info.app_invite_user_id:
            invite_user = User.get_by_id(user_info.app_invite_user_id)
            invite_name = invite_user.nickname
            invite_mobile = invite_user.mobile
        root_name = ''
        root_mobile = ''
        if user_info.app_root_invite_user_id:
            root_user = User.get_by_id(user_info.app_root_invite_user_id)
            root_name = root_user.nickname
            root_mobile = root_user.mobile
        invite_cnt = UserInfo.query.filter(
            UserInfo.app_invite_user_id == each.id
        ).count()
        res.append(UserStat(
            each.id, each.mobile, each.nickname,
            invite_name, invite_mobile,
            root_name, root_mobile, invite_cnt,
            datetime.fromtimestamp(each.created_at)
        ))
    return render_template(
        'stat/app_promotion_stat.html',
        stats=res,
        pagination=pagination,
        url_kwargs=url_kwargs,
        form=form
    )


@op_bp.route('/app/promotion/cens', methods=['GET', 'POST'])
@login_required
def app_promotion_cens():
    """
    created by  at 2018/5/

    """
    url_kwargs = {}
    form = SearchAppPromotionCensForm(
        formdata=request.form or request.args
    )

    query = UserInfo.query.filter(
        User.id == UserInfo.app_root_invite_user_id
    ).group_by(
        UserInfo.app_root_invite_user_id
    ).order_by(
        User.created_at.desc()
    )

    if form.validate():
        if (
            form.app_root_invite_user_id.data
            and form.app_root_invite_user_id.data > -1
        ):
            query = query.filter(
                UserInfo.app_root_invite_user_id == form.app_root_invite_user_id.data
            )
    query = query.with_entities(
        User.id, User.mobile, User.nickname, User.created_at
    )
    page = request.args.get('page', 1, type=int)
    pagination = query.paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    stats = pagination.items
    res = []

    UserStat = namedtuple(
        'UserStat',
        'id root_name root_mobile invite_cnt created_at'
    )

    for each in stats:

        invite_cnt = UserInfo.query.filter(
            UserInfo.app_root_invite_user_id == each.id
        ).count()

        res.append(UserStat(
            each.id, each.nickname, each.mobile, invite_cnt,
            datetime.fromtimestamp(each.created_at)
        ))
    return render_template(
        'stat/app_promotion_cens.html',
        stats=res,
        pagination=pagination,
        url_kwargs=url_kwargs,
        form=form
    )


@op_bp.route('/operate/stat/export', methods=['GET', 'POST'])
@login_required
def operate_stat_export():
    """
    created by chenhj at 2018/7/2
    运营需要的导出
    """
    form = OperateExportForm()
    today = date.today()
    if not form.start_date.data:
        form.start_date.data = date(today.year, today.month, 1)
    if form.validate_on_submit():
        start = form.start_date.data.strftime('%s')
        end = (form.end_date.data + timedelta(days=1)).strftime('%s')
        email = form.email.data
        current_app.common_producer.send(
            json.dumps(
                dict(
                    backend='demand_backend',
                    func='operation_required_export',
                    kwargs=dict(
                        start=start,
                        end=end,
                        email=email,
                    )
                )
            )
        )
        flash('操作成功，请稍后到您的邮箱查看导出数据')
        return redirect(url_for('op_bp.operate_stat_export'))
    return render_template(
        'stat/operate_stat_export.html',
        form=form,
    )


@op_bp.route('/operate/stat/export/app_users')
@login_required
def operate_stat_export_app_users():
    """
    created by lina at 2018/7/19
    """
    app_user_query = UserInfo.query.join(
        User,
        UserInfo.user_id == User.id
    ).with_entities(
        UserInfo.app_last_visit_time,
        UserInfo.app_first_login_time,
        UserInfo.app_login_version,
        User.id,
        User.mobile,
        User.nickname
    ).filter(
        UserInfo.app_last_visit_time.isnot(None),
    ).order_by(
        UserInfo.app_last_visit_time.desc()
    )

    book = xlwt.Workbook()
    template_path = '/tmp/opc_app_users_{}.xls'.format(get_timestamp())
    fields = ['ID', '手机号', '用户名', 'APP首次登录时间', '最后使用时间', 'APP版本号']
    sheet = book.add_sheet('使用APP用户名单')

    for index, value in enumerate(fields):
        sheet.write(0, index, value)
    data = [
        (
            i.id,
            i.mobile,
            i.nickname,
            i.app_first_login_time.strftime('%F %T') if i.app_first_login_time else '',
            i.app_last_visit_time.strftime('%F %T'),
            i.app_login_version
        ) for i in app_user_query
    ]
    for index, each in enumerate(data):
        for num, value in enumerate(each):
            sheet.write(index+1, num, value)
    book.save(template_path)
    if not os.path.exists(template_path):
        abort(404)
    return send_file(template_path, as_attachment=True)


@op_bp.route('/voucher/stat', methods=['GET', 'POST'])
@login_required
def voucher_stat():
    """
    created by chenhj at 2018/7/9

    """
    form = DefaultStatSearchForm(formdata=request.form or request.args)
    today = date.today()
    if not form.start_date.data:
        form.start_date.data = date(today.year, today.month, 1)

    query = VoucherDayStat.query
    url_kwargs = {}
    if form.validate():
        query_kwargs, url_kwargs = form.generate_kwargs()

        query = VoucherDayStat.generate_list_query_condition(
            args=query_kwargs,
            query=query
        )

    page = request.args.get('page', 1, type=int)
    pagination = query.order_by(VoucherDayStat.stat_date.desc()).paginate(
        page, per_page=1000,
        error_out=False)
    data = pagination.items

    return render_template(
        'stat/voucher_stat_list.html',
        form=form,
        data=data,
        pagination=pagination,
        url_kwargs=url_kwargs,
    )


@op_bp.route('/voucher/stat/<int:voucher_stat_type>', methods=['GET', 'POST'])
@login_required
def voucher_type_stat(voucher_stat_type):
    """
    create by ld_wu 2018-07-18
    """
    form = DefaultStatSearchForm(formdata=request.form or request.args)
    today = date.today()
    if not form.start_date.data:
        form.start_date.data = date(today.year, today.month, 1)

    voucher_stat_type = VoucherStatType(voucher_stat_type)
    data = VoucherDailyStat.voucher_stat(**form.data)
    return render_template(
        'stat/voucher_daily_stat_list.html',
        form=form,
        voucher_stat_type=voucher_stat_type,
        data=data
    )


@op_bp.route('/stock/stat', methods=['GET', 'POST'])
@login_required
def stock_stat():
    """
    created by chenhj at 2018/7/9
    存量统计
    """
    form = DefaultStatSearchForm(formdata=request.form or request.args)
    today = date.today()
    if not form.start_date.data:
        form.start_date.data = date(today.year, today.month, 1)

    query = StockDayStat.query
    url_kwargs = {}
    if form.validate():
        query_kwargs, url_kwargs = form.generate_kwargs()

        query = StockDayStat.generate_list_query_condition(
            args=query_kwargs,
            query=query
        )

    page = request.args.get('page', 1, type=int)
    pagination = query.order_by(StockDayStat.stat_date.desc()).paginate(
        page, per_page=1000,
        error_out=False)
    data = pagination.items

    return render_template(
        'stat/stock_stat_list.html',
        form=form,
        data=data,
        pagination=pagination,
        url_kwargs=url_kwargs,
    )


@op_bp.route('/demand_day_stat_list', methods=['GET', 'POST'])
@login_required
def demand_day_stat_list():
    form = DefaultStatSearchForm()
    today = date.today()
    if not form.start_date.data:
        form.start_date.data = date(today.year, today.month, 1)
    stat_query = DemandDayStat.query
    url_kwargs = {}
    if form.validate():
        query_kwargs, url_kwargs = form.generate_kwargs()

        stat_query = DemandDayStat.generate_list_query_condition(
            args=query_kwargs,
            query=stat_query
        )
    page = request.args.get('page', 1, type=int)
    pagination = stat_query.order_by(DemandDayStat.stat_date.desc()).paginate(
        page, per_page=50,
        error_out=False)
    data = pagination.items

    return render_template(
        'stat/demand_day_stat_list.html',
        form=form,
        data=data,
        pagination=pagination,
        url_kwargs=url_kwargs
    )


@op_bp.route('/app_usage_list', methods=['GET', 'POST'])
@login_required
def app_usage_list():
    app_user_query = UserInfo.query.join(
        User,
        UserInfo.user_id == User.id
    ).with_entities(
        UserInfo.app_last_visit_time,
        UserInfo.app_first_login_time,
        UserInfo.app_login_version,
        User.id,
        User.mobile,
        User.nickname
    ).filter(
        UserInfo.app_last_visit_time.isnot(None),
    ).order_by(
        UserInfo.app_last_visit_time.desc()
    )
    url_kwargs = {}
    page = request.args.get('page', 1, type=int)
    pagination = app_user_query.paginate(
        page, per_page=current_app.config['FLASKY_COMMENTS_PER_PAGE'],
        error_out=False)
    data = pagination.items

    return render_template(
        'stat/app_usage_list.html',
        data=data,
        url_kwargs=url_kwargs,
        pagination=pagination
    )